import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple
from decimal import Decimal

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历_不同院系下_不同专业的人数及占比_以及院系和专业的数量(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历，不同院系下，不同专业的人数及占比；以及院系和专业的数量 - 指标计算函数
    
    ## 指标说明
    该指标用于统计不同学历下，各院系和各专业的学生人数及其占比，同时计算院系和专业的数量。
    包含以下数据：
    1. 院系总数和专业总数（汇总数据）
    2. 各院系下的专业分布及其人数占比（层级结构数据）
    3. 各专业的人数及占比（细分数据）
    4. 各院系的人数及占比（细分数据）
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        education (Optional[str]): 学历筛选条件，可选值为[本科毕业生，专科毕业生，硕士研究生，博士研究生]
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "education": "本科毕业生"
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "calcVals": [
                    {
                        "val": [
                            {
                                "key": "院系总数",
                                "val": "11",
                                "type": "summary"
                            },
                            {
                                "key": "专业总数",
                                "val": "62",
                                "type": "summary"
                            }
                        ]
                    }
                ]
            },
            {
                "calcVals": [
                    {
                        "val": [
                            {
                                "key": "计算机学院",
                                "val": {
                                    "college": "计算机学院",
                                    "college_count": 500,
                                    "college_ratio": 0.25,
                                    "majors": [
                                        {
                                            "spec": "计算机科学与技术",
                                            "count": 300,
                                            "ratio": 0.15
                                        },
                                        {
                                            "spec": "软件工程",
                                            "count": 200,
                                            "ratio": 0.10
                                        }
                                    ]
                                },
                                "type": "hierarchy",
                                "category": "college_major"
                            }
                        ]
                    }
                ]
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历，不同院系下，不同专业的人数及占比；以及院系和专业的数量, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM dp_njc.client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 构建动态表名
        student_table = f"dp_njc.dim_client_target_baseinfo_student_calc_{item_year}"
        
        # 4. 构建学历筛选条件
        education_filter = ""
        if education:
            education_filter = f"AND education = '{education}'"
        
        # 5. 计算院系数量
        college_count_sql = f"""
        SELECT 
            count(distinct college) as count
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
            AND college != ''
            {education_filter}
        """
        college_count = db.fetchone(college_count_sql, (shard_tb_key,))
        if not college_count:
            raise ValueError("计算院系数量失败")
        
        # 6. 计算专业数量
        major_count_sql = f"""
        SELECT 
            count(distinct spec) as count
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
            AND spec != ''
            {education_filter}
        """
        major_count = db.fetchone(major_count_sql, (shard_tb_key,))
        if not major_count:
            raise ValueError("计算专业数量失败")
        
        # 7. 计算各专业人数及占比
        major_stats_sql = f"""
        SELECT 
            spec, 
            count(*) as count, 
            count(*)/(SELECT count(*) FROM {student_table} WHERE shard_tb_key = %s {education_filter}) AS ratio_val 
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
            AND spec != ''
            {education_filter}
        GROUP BY
            spec
        ORDER BY ratio_val DESC
        """
        major_stats = db.fetchall(major_stats_sql, (shard_tb_key, shard_tb_key))
        
        # 8. 计算各院系人数及占比
        college_stats_sql = f"""
        SELECT 
            college, 
            count(*) as count, 
            count(*)/(SELECT count(*) FROM {student_table} WHERE shard_tb_key = %s {education_filter}) AS ratio_val 
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
            AND college != ''
            {education_filter}
        GROUP BY
            college
        ORDER BY ratio_val DESC
        """
        college_stats = db.fetchall(college_stats_sql, (shard_tb_key, shard_tb_key))
        
        # 9. 计算院系-专业层级结构数据
        college_major_sql = f"""
        SELECT 
            college,
            spec,
            count(*) as count,
            count(*)/(SELECT count(*) FROM {student_table} WHERE shard_tb_key = %s {education_filter}) AS ratio_val
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
            AND college != ''
            AND spec != ''
            {education_filter}
        GROUP BY
            college, spec
        ORDER BY count DESC, college, spec
        """
        college_major_stats = db.fetchall(college_major_sql, (shard_tb_key, shard_tb_key))
        
        # 10. 构建业务系统标准格式的返回结果
        result_data = []
        
        # 汇总数据：院系总数和专业总数
        summary_data = {
            "calcVals": [
                {
                    "val": [
                        {
                            "key": "院系总数",
                            "val": str(college_count['count']),
                            "type": "summary"
                        },
                        {
                            "key": "专业总数",
                            "val": str(major_count['count']),
                            "type": "summary"
                        }
                    ]
                }
            ]
        }
        result_data.append(summary_data)
        
        # 层级结构数据：院系-专业分布（按人数排序）
        if college_major_stats:
            # 按院系分组整理数据
            college_groups = {}
            for item in college_major_stats:
                college = item['college']
                if college not in college_groups:
                    college_groups[college] = []
                college_groups[college].append({
                    'spec': item['spec'],
                    'count': item['count'],
                    'ratio': item['ratio_val']
                })
            
            # 获取院系占比信息
            college_ratio_map = {item['college']: item['ratio_val'] for item in college_stats}
            
            # 计算院系总人数并排序
            college_totals = []
            for college, majors in college_groups.items():
                college_total = sum(major['count'] for major in majors)
                college_totals.append((college, college_total, majors))
            
            # 按院系人数从高到低排序
            college_totals.sort(key=lambda x: x[1], reverse=True)
            
            # 构建院系-专业层级数据
            for college, college_total, majors in college_totals:
                college_ratio = college_ratio_map.get(college, 0)
                
                # 院系内专业按人数从高到低排序
                majors_sorted = sorted(majors, key=lambda x: x['count'], reverse=True)
                
                # 构建结构化的专业数据
                majors_data = [
                    {
                        "spec": major['spec'],
                        "count": int(major['count']),
                        "ratio": float(major['ratio'])
                    }
                    for major in majors_sorted
                ]
                
                hierarchy_data = {
                    "calcVals": [
                        {
                            "val": [
                                {
                                    "key": college,
                                    "val": {
                                        "college": college,
                                        "college_count": int(college_total),
                                        "college_ratio": float(college_ratio),
                                        "majors": majors_data
                                    },
                                    "type": "hierarchy",
                                    "category": "college_major"
                                }
                            ]
                        }
                    ]
                }
                result_data.append(hierarchy_data)
        
        logger.info(f"指标 '各学历，不同院系下，不同专业的人数及占比；以及院系和专业的数量' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result_data
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历，不同院系下，不同专业的人数及占比；以及院系和专业的数量' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历，不同院系下，不同专业的人数及占比；以及院系和专业的数量",
            "code": 500,
            "error": str(e)
        }